﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Text;


namespace DAO.WaiGaoQiao
{
    public class DataAccessGuest
    {
        //public int Save(GuestInfo ginfo)
        //{
        //    using (SqlConnection conn = new SqlConnection("server=localhost;database=TestDb;uid=sa;pwd=11"))
        //    {
        //        conn.Open();
        //        string insql = "Insert into Tbt_guest(GuestNo,GuestID,GuestName) values('" + ginfo.Guestno + "','"
        //                        + ginfo.Guestid + "','" + ginfo.Guestname + "')";
        //        SqlCommand comm = new SqlCommand(insql, conn);
        //        return comm.ExecuteNonQuery();
        //    }
        //}

        public IDataReader GetGuestListForDr(string gid)
        {
            using (SqlConnection conn = new SqlConnection("server=localhost;database=TestDb;uid=sa;pwd=11"))
            {
                conn.Open();
                string insql = "select GuestNo,GuestID,GuestName from Tbt_guest where GuestID = '" + gid + "'";

                SqlCommand comm = new SqlCommand(insql, conn);

                //IList<GuestInfo> glist = new List<GuestInfo>();
                IDataReader sdr = comm.ExecuteReader();
                return sdr;               
            }
        }

        //public IList<GuestInfo> GetGuestListForLi(string gid, int pageSize, int startIndex)
        //{
        //    using (SqlConnection conn = new SqlConnection("server=localhost;database=TestDb;uid=sa;pwd=11"))
        //    {
        //        conn.Open();
        //        int Prev = startIndex * pageSize + 1;
        //        int Next = pageSize * (startIndex - 1);
        //        string insql = "select A.GuestNo,A.GuestID,A.GuestName FROM "
        //                      + "(select ROW_NUMBER() OVER(ORDER BY GuestNo) AS Num,GuestNo,GuestID,GuestName "
        //                      + "from Tbt_guest where 1=1 " + gid + ") A "
        //                      + "WHERE Num < " + Prev.ToString() + " and Num > " + Next.ToString();

        //        SqlCommand comm = new SqlCommand(insql, conn);
        //        SqlDataReader sdr = comm.ExecuteReader();
        //        IList<GuestInfo> glist = new List<GuestInfo>();
        //        GuestInfo ginfo = null;
        //        while (sdr.Read())
        //        {
        //            ginfo = new GuestInfo();
        //            ginfo.Guestno = sdr.GetString(0);
        //            ginfo.Guestid = sdr.GetString(1);
        //            ginfo.Guestname = sdr.GetString(2);
        //            glist.Add(ginfo);
        //        }
        //        sdr.Close();
        //        return glist;
        //    }
        //}

        public int GuestRows(string condition)
        {
            using (SqlConnection conn = new SqlConnection("server=localhost;database=TestDb;uid=sa;pwd=11"))
            {
                conn.Open();

                string insql = "select count(1) from Tbt_guest where 1=1 "+condition;
                SqlCommand comm = new SqlCommand(insql, conn);
                return (int)comm.ExecuteScalar();                
            }
        }
    }
}
